from sqlalchemy import Column, Integer, String, create_engine, \
    select, delete, update, func
from sqlalchemy.orm import declarative_base, Session

# declarative base class
Base = declarative_base()


class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)


class Product(Base):
    __tablename__ = 'product'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    ownerId = Column(Integer)


# https://www.osgeo.cn/sqlalchemy/dialects/mysql.html
url = 'mysql+mysqlconnector://ly:123456@localhost/test'
engine = create_engine(url)
sesson = Session(bind=engine)
for person in sesson.query(Person).all():
    print(person.id, person.name, person.age)

print('*' * 20)

for person in sesson.query(Person).filter(Person.age < 30):
    print(person.id, person.name, person.age)

print('*' * 20)

for person in sesson.execute(select(Person).where(Person.age < 30)):
    person = person[0]
    print(person.id, person.name, person.age)

print('*' * 20)

statement = sesson.execute(select(Person.name, func.count(Product.ownerId))
                           .join(Product, Person.id == Product.ownerId)
                           .group_by(Product.ownerId))
for res in statement:
    print(res)

sesson.add(Person(name='Lucy', age=25))
sesson.commit()

sesson.add_all([])
sesson.commit()
